package com.shnu.work.repository;

import com.shnu.work.entity.UserDataWhileUsingEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;


/**
 * @author Shinomiya Kaguya
 */
@Repository
public interface UserDataWhileUsingRepository extends CrudRepository<UserDataWhileUsingEntity, Integer> {
    /**
     * 根据userId查到所有结果
     *
     * @param userId userId
     * @return 所有结果
     */
    @Query(value = "SELECT * FROM user_data_while_using WHERE user_id = ?1 and is_deleted = 0", nativeQuery = true)
    List<UserDataWhileUsingEntity> listUserDataWhileUsingEntitiesByUserId(Long userId);

    /**
     * 根据userDocumentTime和userId查到结果
     *
     * @param userDocumentTime userDocumentTime
     * @param userId           userId
     * @return 结果
     */
    @Query(value = "SELECT * FROM user_data_while_using WHERE user_document_time = ?1 AND user_id = ?2 AND is_deleted = 0", nativeQuery = true)
    UserDataWhileUsingEntity getUserDataWhileUsingEntityByUserDocumentTimeAndUserId(String userDocumentTime, Long userId);

    /**
     * 根据id删除
     *
     * @param id id
     * @return 影响行数
     */
    @Modifying
    @Query(value = "UPDATE user_data_while_using SET is_deleted = 1 WHERE id = ?1", nativeQuery = true)
    Integer removeUserDataById(Long id);

    /**
     * 查找未删除的结果
     *
     * @return 结果list
     */
    @Query(value = "SELECT * FROM user_data_while_using WHERE is_deleted = 0", nativeQuery = true)
    List<UserDataWhileUsingEntity> listAllUndeleted();

    /**
     * 根据设备id查询
     *
     * @param deviceId 设备id
     * @return 结果
     */
    @Query(value = "SELECT * FROM user_data_while_using WHERE device_id = ?1 and is_deleted = 0", nativeQuery = true)
    List<UserDataWhileUsingEntity> listUserDataByDeviceId(Long deviceId);


    /**
     * 根据时间戳查询
     *
     * @param userDocumentTime 时间戳
     * @return 结果
     */
    @Query(value = "SELECT * FROM user_data_while_using WHERE user_document_time like concat('%', ?1, '%')  and is_deleted = 0", nativeQuery = true)
    List<UserDataWhileUsingEntity> listUserDataByUserDocumentTime(String userDocumentTime);

    /**
     * 根据时间戳和设备id查询
     *
     * @param deviceId         设备id
     * @param userDocumentTime 时间戳
     * @return 结果
     */
    @Query(value = "SELECT * FROM user_data_while_using WHERE device_id = ?1 and user_document_time like concat('%', ?2, '%')  and is_deleted = 0", nativeQuery = true)
    List<UserDataWhileUsingEntity> listUserDataByDeviceIdAndUserDocumentTime(Long deviceId, String userDocumentTime);

    /**
     * 根据时间戳和设备id查询
     *
     * @param userDocumentTime 时间戳
     * @param deviceId         设备id
     * @return 行数
     */
    @Modifying
    @Query(value = "update user_data_while_using set is_deleted = 1 where user_document_time = ?1 and device_id = ?2", nativeQuery = true)
    Integer removeUserDataByUserDocumentTimeAndUserName(String userDocumentTime, Long deviceId);

    /**
     * 根据用户id和设备id查询
     *
     * @param deviceId
     * @param userId
     * @return
     */
    @Query(value = "SELECT * FROM user_data_while_using WHERE device_id = ?1 and user_id = ?2 and is_deleted = 0", nativeQuery = true)
    List<UserDataWhileUsingEntity> listUserDataByDeviceIdAndUserId(Long deviceId, Long userId);

    /**
     * 根据时间戳和用户id查询
     *
     * @param userDocumentTime
     * @param userId
     * @return
     */
    @Query(value = "SELECT * FROM user_data_while_using WHERE user_document_time like concat('%', ?1, '%') and user_id = ?2  and is_deleted = 0", nativeQuery = true)
    List<UserDataWhileUsingEntity> listUserDataByUserDocumentTimeAndUserId(String userDocumentTime, Long userId);

    /**
     * 根据时间戳和设备id和用户id查询
     *
     * @param deviceId         设备id
     * @param userDocumentTime 时间戳
     * @return 结果
     */
    @Query(value = "SELECT * FROM user_data_while_using WHERE device_id = ?1 and user_document_time like concat('%', ?2, '%') and user_id = ?3 and is_deleted = 0", nativeQuery = true)
    List<UserDataWhileUsingEntity> listUserDataByDeviceIdAndUserDocumentTimeAndUserId(Long deviceId, String userDocumentTime, Long userId);
}
